-- Add corporate_id and session_number to qr_sessions for corporate-based QR linking
-- Make seminar_id nullable to support QR sessions without seminars

ALTER TABLE qr_sessions ADD COLUMN IF NOT EXISTS corporate_id UUID REFERENCES corporates(corporate_id);
ALTER TABLE qr_sessions ADD COLUMN IF NOT EXISTS session_number INT;

-- Make seminar_id nullable
ALTER TABLE qr_sessions ALTER COLUMN seminar_id DROP NOT NULL;

-- Backfill corporate_id and session_number from seminar for existing rows
UPDATE qr_sessions q
SET corporate_id = s.corporate_id,
    session_number = 1
FROM seminars s
WHERE q.seminar_id = s.seminar_id AND q.corporate_id IS NULL;

-- Add qr_session_id to seminar_scans to link scans to QR sessions (corporate + session)
ALTER TABLE seminar_scans ADD COLUMN IF NOT EXISTS qr_session_id UUID REFERENCES qr_sessions(qr_id);
ALTER TABLE seminar_scans ALTER COLUMN seminar_id DROP NOT NULL;

CREATE INDEX IF NOT EXISTS idx_qr_sessions_corporate ON qr_sessions(corporate_id);
CREATE INDEX IF NOT EXISTS idx_seminar_scans_qr_session ON seminar_scans(qr_session_id);
